/**
 * 
 */
package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import entity.*;
import utils.DBHelper;

/**
 * @author Chen Lei
 *
 */
public class CommentDAO {
	private CommentDAO() {
	}

	public static CommentDAO getInstance() {
		return new CommentDAO();
	}
	/**
	 * 得到某帖子的所有评论
	 *
	 */
	public List<Comment> list(int article_id) {
		return list(0, Short.MAX_VALUE, article_id);
	}
	/**
	 * 得到某帖子从start开始count个评论详情
	 *
	 */
	public List<Comment> list(int start, int count, int article_id) {
		List<Comment> comments = new ArrayList<Comment>();

		try {

			Connection c = DBHelper.getInstance().getConnection();

			String sql = "select * from comment where article_id = "+ article_id +" ORDER BY comment_time DESC limit ?,? ";

			PreparedStatement ps = c.prepareStatement(sql);
			ps.setInt(1, start);
			ps.setInt(2, count);

			ResultSet rs = ps.executeQuery();

			while (rs.next()) {
				Comment cm = new Comment();
				
				int comment_id = rs.getInt("comment_id");
				String comment_content = rs.getString("comment_content");
				int user_id = rs.getInt("user_id");
				Date comment_time = rs.getDate("comment_time");
				int comment_zan_num = rs.getInt("comment_zan_num");
				cm.setComment_content(comment_content);
				cm.setComment_id(comment_id);
				cm.setArticle_id(article_id);
				cm.setUser_id(user_id);
				cm.setComment_time(comment_time);
				cm.setComment_zan_num(comment_zan_num);
				
				comments.add(cm);
			}
			DBHelper.closeConnection(c, ps, rs);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return comments;
	}
	/**
	 * 按时间倒序得到所有帖子的最新评论，显示在主页的最新评论模板
	 *
	 */
	public List<Comment> list_new() {
		return list_new(0, Short.MAX_VALUE);
	}
	/**
	 * 得到从start开始count个评论详情
	 *
	 */
	public List<Comment> list_new(int start, int count) {
		List<Comment> comments = new ArrayList<Comment>();

		try {

			Connection c = DBHelper.getInstance().getConnection();

			String sql = "select * from comment ORDER BY comment_time DESC limit ?,? ";

			PreparedStatement ps = c.prepareStatement(sql);
			ps.setInt(1, start);
			ps.setInt(2, count);

			ResultSet rs = ps.executeQuery();

			while (rs.next()) {
				Comment cm = new Comment();
				
				int comment_id = rs.getInt("comment_id");
				int article_id = rs.getInt("article_id");
				String comment_content = rs.getString("comment_content");
				int user_id = rs.getInt("user_id");
				Date comment_time = rs.getDate("comment_time");
				int comment_zan_num = rs.getInt("comment_zan_num");
				
				cm.setComment_content(comment_content);
				cm.setComment_id(comment_id);
				cm.setArticle_id(article_id);
				cm.setUser_id(user_id);
				cm.setComment_time(comment_time);
				cm.setComment_zan_num(comment_zan_num);
				
				comments.add(cm);
			}
			DBHelper.closeConnection(c, ps, rs);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return comments;
	}
	public int add(Comment comment) {

		try {

			Connection c = DBHelper.getInstance().getConnection();

			String sql = "insert into comment values(null,?,?,?,?,0)";
			PreparedStatement ps = c.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
			ps.setInt(1, comment.getArticle_id());
			ps.setInt(2, comment.getUser_id());
			ps.setString(3, comment.getComment_content());
			ps.setDate(4,new java.sql.Date(new Date().getTime()));
			
			ps.execute();

			ResultSet rs = ps.getGeneratedKeys();
			if (rs.next()) {
				int comment_id =  rs.getInt(1);
				return comment_id;
			}

			DBHelper.closeConnection(c, ps, null);

		} catch (Exception e) {
			e.printStackTrace();
		}
		return -1;
	}
	public Comment getByID(int comment_id){
		Comment comment = new Comment();

		try {

			Connection c = DBHelper.getInstance().getConnection();

			String sql = "select * from comment where comment_id = "+ comment_id;

			PreparedStatement ps = c.prepareStatement(sql);


			ResultSet rs = ps.executeQuery();

			if(rs.next()) {			
				
				int article_id = rs.getInt("article_id");
				String comment_content = rs.getString("comment_content");
				int user_id = rs.getInt("user_id");
				Date comment_time = rs.getDate("comment_time");
				int comment_zan_num = rs.getInt("comment_zan_num");
				comment.setComment_content(comment_content);
				comment.setComment_id(comment_id);
				comment.setArticle_id(article_id);
				comment.setUser_id(user_id);
				comment.setComment_time(comment_time);
				comment.setComment_zan_num(comment_zan_num);

			}
			DBHelper.closeConnection(c, ps, rs);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return comment;
		
	}
	public List<Comment> get(int article_id){
		List<Comment> comments = new ArrayList<Comment>();

		try {

			Connection c = DBHelper.getInstance().getConnection();

			String sql = "select * from comment where article_id = "+ article_id;

			PreparedStatement ps = c.prepareStatement(sql);


			ResultSet rs = ps.executeQuery();

			while (rs.next()) {
				Comment cm = new Comment();
				
				int comment_id = rs.getInt("comment_id");
				String comment_content = rs.getString("comment_content");
				int user_id = rs.getInt("user_id");
				Date comment_time = rs.getDate("comment_time");
				int comment_zan_num = rs.getInt("comment_zan_num");
				cm.setComment_content(comment_content);
				cm.setComment_id(comment_id);
				cm.setArticle_id(article_id);
				cm.setUser_id(user_id);
				cm.setComment_time(comment_time);
				cm.setComment_zan_num(comment_zan_num);
				//int user_id1=getByID(cm.getComment_id()).getUser_id();
				cm.setUser_name(UserDAO.getInstance().get(user_id).getUser_name());
				
				comments.add(cm);
			}
			DBHelper.closeConnection(c, ps, rs);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return comments;
		
	}
}
